﻿using System.Data;
using System.Data.SqlClient;

namespace Adams.Public.DataAccess
{
    public static class Profile
    {
        public static Adams.Public.DataTransfer.Profile.GetMember GetMember(Adams.Public.DataTransfer.Profile.GetMemberInput input)
        {
            var data = new Adams.Public.DataTransfer.Profile.GetMember();
            data.Member = new Shared.Entities.Membership.Member();

            using (SqlDataReader dr = DB.RunDataReader(@"
SELECT M.*
FROM Membership.Members M
WHERE M.MemberID = @MemberID
", CommandType.Text,
    DB.CreateParameter("@MemberID", input.MemberID)))
            {
                dr.Read();
                data.Member.ID = dr["MemberID"].DbInt();
                data.Member.Email = dr["Email"].DbString();
                data.Member.FirstName = dr["FirstName"].DbString();
                data.Member.LastName = dr["LastName"].DbString();
                data.Member.Street = dr["Street"].DbString();
                data.Member.Street2 = dr["Street2"].DbString();
                data.Member.City = dr["City"].DbString();
                data.Member.State = dr["State"].DbString();
                data.Member.ZipCode = dr["ZipCode"].DbString();
                data.Member.Phone = dr["Phone"].DbString();
            }

            return data;
        }

        public static Adams.Public.DataTransfer.Profile.MemberInfo GetMemberInfo(int memberId, string email)
        {
            var data = new Adams.Public.DataTransfer.Profile.MemberInfo();

            using (SqlDataReader dr = DB.RunDataReader(@"
IF (@MemberID > 0)
BEGIN
    SELECT *
    FROM Membership.Members
    WHERE MemberID = @MemberID
END

SELECT COUNT(*) AS Count
FROM Membership.Members
WHERE Email = @Email
", CommandType.Text,
                DB.CreateParameter("@MemberID", memberId),
                DB.CreateParameter("@Email", email)))
            {
                if (memberId > 0)
                {
                    dr.Read();
                    data.Email = dr["Email"].DbString();
                    data.PasswordHash = dr["PasswordHash"].DbString();
                    data.PasswordSalt = dr["PasswordSalt"].DbString();
                    dr.NextResult();
                }
                dr.Read();
                data.Exists = dr["Count"].DbInt() > 0;
            }

            return data;
        }

        public static void SaveEmail(Adams.Public.DataTransfer.Profile.SaveEmailInput input)
        {
            DB.Run(@"
UPDATE Membership.Members SET
    Email = @Email, IsEmailConfirmed = 0
WHERE MemberID = @MemberID
", CommandType.Text,
                    DB.CreateParameter("@MemberID", input.MemberID),
                    DB.CreateParameter("@Email", input.Email));
        }

        public static void SaveGeneral(Adams.Public.DataTransfer.Profile.SaveGeneralInput input)
        {
            DB.Run(@"
UPDATE Membership.Members SET
    FirstName = @FirstName
    ,LastName = @LastName
    ,Street = @Street
    ,Street2 = @Street2
    ,City = @City
    ,State = @State
    ,ZipCode = @ZipCode
    ,Phone = @Phone
WHERE MemberID = @MemberID
", CommandType.Text,
                    DB.CreateParameter("@MemberID", input.MemberID),
                    DB.CreateParameter("@FirstName", input.FirstName),
                    DB.CreateParameter("@LastName", input.LastName),
                    DB.CreateParameter("@Street", input.Street),
                    DB.CreateParameter("@Street2", input.Street2),
                    DB.CreateParameter("@City", input.City),
                    DB.CreateParameter("@State", input.State),
                    DB.CreateParameter("@ZipCode", input.ZipCode),
                    DB.CreateParameter("@Phone", input.Phone));
        }

        public static void SavePassword(int memberId, string passwordHash)
        {
            DB.Run(@"
UPDATE Membership.Members SET
    PasswordHash = @PasswordHash
WHERE MemberID = @MemberID
", CommandType.Text,
                    DB.CreateParameter("@MemberID", memberId),
                    DB.CreateParameter("@PasswordHash", passwordHash));
        }

        public static Adams.Public.DataTransfer.Profile.MemberInfo GetMemberInfo(int memberId)
        {
            var data = new Adams.Public.DataTransfer.Profile.MemberInfo();

            using (SqlDataReader dr = DB.RunDataReader(@"
SELECT *
FROM Membership.Members
WHERE MemberID = @MemberID
", CommandType.Text,
                DB.CreateParameter("@MemberID", memberId)))
            {
                dr.Read();
                data.PasswordHash = dr["PasswordHash"].DbString();
                data.PasswordSalt = dr["PasswordSalt"].DbString();
            }

            return data;
        }

        public static void SaveCreditCard(Adams.Public.DataTransfer.Profile.SaveCreditCardSave input)
        {
            DB.Run(@"
IF NOT EXISTS(SELECT CreditCardID FROM Finance.CreditCards WHERE MemberID = @MemberID)
BEGIN
    INSERT INTO Finance.CreditCards(
    MemberID
    ,Title
    ,FirstName
    ,LastName
    ,Street
    ,Street2
    ,City
    ,State
    ,ZipCode
    ,Phone
    ,CardHolderName
    ,CardNumber
    ,CardType
    ,CardLast4Digits
    ,CardExpireMonth
    ,CardExpireYear
    ,CardSecurityCode)
    VALUES (
    @MemberID
    ,@Title
    ,@FirstName
    ,@LastName
    ,@Street
    ,@Street2
    ,@City
    ,@State
    ,@ZipCode
    ,@Phone
    ,@CardHolderName
    ,@CardNumber
    ,@CardType
    ,@CardLast4Digits
    ,@CardExpireMonth
    ,@CardExpireYear
    ,@CardSecurityCode)
END
ELSE
BEGIN
    UPDATE Finance.CreditCards SET
        Title = @Title
        ,FirstName = @FirstName
        ,LastName = @LastName
        ,Street = @Street
        ,Street2 = @Street2
        ,City = @City
        ,State = @State
        ,ZipCode = @ZipCode
        ,Phone = @Phone
        ,CardHolderName = @CardHolderName
        ,CardNumber = @CardNumber
        ,CardType = @CardType
        ,CardLast4Digits = @CardLast4Digits
        ,CardExpireMonth = @CardExpireMonth
        ,CardExpireYear = @CardExpireYear
        ,CardSecurityCode = @CardSecurityCode
    WHERE MemberID = @MemberID
END
", CommandType.Text,
                    DB.CreateParameter("@MemberID", input.MemberID),
                    DB.CreateParameter("@Title", input.Title.ToNString()),
                    DB.CreateParameter("@FirstName", input.FirstName),
                    DB.CreateParameter("@LastName", input.LastName),
                    DB.CreateParameter("@Street", input.Street),
                    DB.CreateParameter("@Street2", input.Street2.ToNString()),
                    DB.CreateParameter("@City", input.City),
                    DB.CreateParameter("@State", input.State),
                    DB.CreateParameter("@ZipCode", input.ZipCode),
                    DB.CreateParameter("@Phone", input.Phone),
                    DB.CreateParameter("@CardHolderName", input.CardHolderName),
                    DB.CreateParameter("@CardNumber", input.CardNumber),
                    DB.CreateParameter("@CardType", input.CardType),
                    DB.CreateParameter("@CardLast4Digits", input.Last4Digits),
                    DB.CreateParameter("@CardExpireMonth", input.CardExpireMonth),
                    DB.CreateParameter("@CardExpireYear", input.CardExpireYear),
                    DB.CreateParameter("@CardSecurityCode", input.CardSecurityCode));
        }

        public static Adams.Public.DataTransfer.Profile.GetCreditCard GetCreditCard(Adams.Public.DataTransfer.MemberInfo input)
        {
            var data = new Adams.Public.DataTransfer.Profile.GetCreditCard();
            data.CreditCard = new Shared.Entities.Finance.CreditCard();

            using (SqlDataReader dr = DB.RunDataReader(@"
SELECT C.*
FROM Finance.CreditCards C
WHERE C.MemberID = @MemberID
", CommandType.Text,
    DB.CreateParameter("@MemberID", input.MemberID)))
            {
                if (dr.HasRows)
                {
                    dr.Read();
                    data.HasCreditCard = true;
                    data.CreditCard.ID = dr["CreditCardID"].DbInt();
                    data.CreditCard.Title = dr["Title"].DbString();
                    data.CreditCard.FirstName = dr["FirstName"].DbString();
                    data.CreditCard.LastName = dr["LastName"].DbString();
                    data.CreditCard.Street = dr["Street"].DbString();
                    data.CreditCard.Street2 = dr["Street2"].DbString();
                    data.CreditCard.City = dr["City"].DbString();
                    data.CreditCard.State = dr["State"].DbString();
                    data.CreditCard.ZipCode = dr["ZipCode"].DbString();
                    data.CreditCard.Phone = dr["Phone"].DbString();
                    data.CreditCard.CardHolderName = dr["CardHolderName"].DbString();
                    data.CreditCard.CardType = dr["CardType"].DbString();
                    data.CreditCard.CardLast4Digits = dr["CardLast4Digits"].DbString();
                    data.CreditCard.CardExpireMonth = dr["CardExpireMonth"].DbInt();
                    data.CreditCard.CardExpireYear = dr["CardExpireYear"].DbInt();
                }
                else
                {
                    data.HasCreditCard = false;
                }
            }

            return data;
        }

    }
}
